#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
-- 意向主题插入语句
INSERT INTO hive.edu_online_dws.dws_relationship
WITH temp AS (
    SELECT
        -- 时间粒度
        -- 年
        substring(create_time,1,4) AS year_code,
        -- 月
        substring(create_time,6,2) AS month_code,
        -- 天
        substring(create_time,9,2) AS day_code,
        -- 维度
        if(origin_type ='NETSERVICE' OR origin_type = 'PRESIGNUP','online','offline') AS origin_type,
        if(clue_state = 'VALID_NEW_CLUES','new_member','old_member') AS clue_state,
        area,
        subject_id,
        subject_name,
        school_id,
        school_name,
        origin_channel,
        department_id,
        department_name,
        -- 指标
        relation_id
    FROM hive.edu_online_dwb.dwb_relationship_detail)
SELECT
        -- 时间粒度
        year_code,
        month_code,
        day_code,
        -- 时间粒度指标
        CASE WHEN GROUPING(year_code,month_code,day_code) = 0
            THEN 'date'
            WHEN GROUPING(year_code,month_code) = 0
            THEN 'month'
            WHEN GROUPING(year_code) = 0
            THEN 'year'
        END AS time_type,

        -- 维度
        CASE WHEN GROUPING(origin_type) = 0
            THEN origin_type
            ELSE NULL END AS origin_type,
        CASE WHEN GROUPING(clue_state) = 0
            THEN clue_state
            ELSE NULL END AS clue_state,
        CASE WHEN GROUPING(area) = 0
            THEN area
            ELSE NULL END AS area,
        CASE WHEN GROUPING(subject_id) = 0
            THEN subject_id
            ELSE NULL END AS subject_id,
        CASE WHEN GROUPING(subject_name) = 0
            THEN subject_name
            ELSE NULL END AS subject_name,
        CASE WHEN GROUPING(school_id) = 0
            THEN school_id
            ELSE NULL END AS school_id,
        CASE WHEN GROUPING(school_name) = 0
            THEN school_name
            ELSE NULL END AS school_name,
        CASE WHEN GROUPING(origin_channel) = 0
            THEN origin_channel
            ELSE NULL END AS origin_channel,
        CASE WHEN GROUPING(department_id) = 0
            THEN department_id
            ELSE NULL END AS department_id,
        CASE WHEN GROUPING(department_name) = 0
            THEN department_name
            ELSE NULL END AS department_name,
        -- 维度指标
        CASE WHEN GROUPING(origin_type,clue_state,department_id) = 0
            THEN 'reference_center'
            WHEN GROUPING(origin_type,clue_state,origin_channel) = 0
            THEN 'origin_channel'
            WHEN GROUPING(origin_type,clue_state,school_id) = 0
            THEN 'school'
            WHEN GROUPING(origin_type,clue_state,subject_id) = 0
            THEN 'subject'
            WHEN GROUPING(origin_type,clue_state,area) = 0
            THEN 'area'
            WHEN GROUPING(origin_type,clue_state) = 0
            THEN 'new_old_member'
            WHEN GROUPING(origin_type) = 0
            THEN 'online_offline'
        END AS group_type,

        -- 意向用户个数
        CASE WHEN GROUPING(origin_type,clue_state,department_id,department_name) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type,clue_state,origin_channel) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type,clue_state,school_id,school_name) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type,clue_state,subject_id,subject_name) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type,clue_state,area) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type,clue_state) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
            WHEN GROUPING(origin_type) = 0
            THEN cast(count(if(relation_id IS NOT NULL,relation_id,NULL)) AS INT )
        END AS relation_num

FROM temp t
GROUP BY
GROUPING SETS (
    -- 年
    (year_code),    -- 年
    (year_code,origin_type),    -- 年+线上线下
    (year_code,origin_type,clue_state),     -- 年+新老学员
    (year_code,origin_type,clue_state,area),        -- 年+线上线下+新老学员+地区
    (year_code,origin_type,clue_state,subject_id,subject_name),     -- 年+线上线下+新老学员+学科
    (year_code,origin_type,clue_state,school_id,school_name),       -- 年+线上线下+新老学员+校区
    (year_code,origin_type,clue_state,origin_channel),       -- 日+线上线下+新老学员+销售渠道
    (year_code,origin_type,clue_state,department_id,department_name),   -- 年+线上线下+新老学员+部门

    -- 月
    (year_code,month_code),    -- 月
    (year_code,month_code,origin_type),     -- 月+线上线下
    (year_code,month_code,origin_type,clue_state),     -- 月+新老学员
    (year_code,month_code,origin_type,clue_state,area),        -- 月+线上线下+新老学员+地区
    (year_code,month_code,origin_type,clue_state,subject_id,subject_name),     -- 月+线上线下+新老学员+学科
    (year_code,month_code,origin_type,clue_state,school_id,school_name),       -- 月+线上线下+新老学员+校区
    (year_code,month_code,origin_type,clue_state,origin_channel),       -- 日+线上线下+新老学员+销售渠道
    (year_code,month_code,origin_type,clue_state,department_id,department_name),   -- 月+线上线下+新老学员+部门

    -- 日
    (year_code,month_code,day_code),    -- 日
    (year_code,month_code,day_code,origin_type),     -- 日+线上线下
    (year_code,month_code,day_code,origin_type,clue_state),     -- 日+新老学员
    (year_code,month_code,day_code,origin_type,clue_state,area),        -- 日+线上线下+新老学员+地区
    (year_code,month_code,day_code,origin_type,clue_state,subject_id,subject_name),     -- 日+线上线下+新老学员+学科
    (year_code,month_code,day_code,origin_type,clue_state,school_id,school_name),       -- 日+线上线下+新老学员+校区
    (year_code,month_code,day_code,origin_type,clue_state,origin_channel),       -- 日+线上线下+新老学员+销售渠道
    (year_code,month_code,day_code,origin_type,clue_state,department_id,department_name)   -- 日+线上线下+新老学员+部门
);


-- 线索主题插入语句
INSERT INTO hive.edu_online_dws.dws_clue
WITH temp AS (
    SELECT
        -- 时间粒度字段
        substring(create_time,1,10) AS day_code,    -- 天
        substring(create_time,12,2) AS hour_code,   -- 小时
        --维度
        if(origin_type ='NETSERVICE' OR origin_type = 'PRESIGNUP','online','offline') AS origin_type,
        if(clue_state = 'VALID_NEW_CLUES','new_member','old_member') AS clue_state,
        -- 指标
        if(appeal_status = 2,'valid','invalid') AS appeal_status,
        clue_id
    FROM hive.edu_online_dwb.dwb_clue_detail)
SELECT
        -- 时间粒度字段（天、小时）
        day_code,   -- 天
        hour_code,  -- 小时
        CASE WHEN GROUPING(day_code,hour_code) = 0
            THEN 'hour'
            WHEN GROUPING(day_code) = 0
            THEN 'date'
        END AS time_type,

        -- 维度字段
        CASE WHEN GROUPING(origin_type) = 0
            THEN origin_type
            ELSE NULL END AS origin_type,
        CASE WHEN GROUPING(clue_state) = 0
            THEN clue_state
            ELSE NULL END AS clue_state,
        -- 维度字段标志
        CASE WHEN GROUPING(origin_type,clue_state) = 0
            THEN 'new_old_member'
            WHEN GROUPING(origin_type) = 0
            THEN 'online_offline'
        END AS time_type,

        -- 指标
        -- 有效线索数
        CASE WHEN GROUPING(origin_type,clue_state) = 0
            THEN cast(count(if(appeal_status = 'valid',appeal_status,NULL)) AS INT)
            WHEN GROUPING(origin_type) = 0
            THEN cast(count(if(appeal_status = 'valid',appeal_status,NULL)) AS INT)
        END AS valid_clue_num,
        -- 线索数
        CASE WHEN GROUPING(origin_type,clue_state) = 0
            THEN cast(count(if(clue_id IS NOT NULL,clue_id,NULL)) AS INT)
            WHEN GROUPING(origin_type) = 0
            THEN cast(count(if(clue_id IS NOT NULL,clue_id,NULL)) AS INT)
        END AS clue_num

FROM temp
GROUP BY
GROUPING SETS (
    -- 天
    (day_code),
    (day_code,origin_type),
    (day_code,origin_type,clue_state),

    -- 小时
    (day_code,hour_code),
    (day_code,hour_code,origin_type),
    (day_code,hour_code,origin_type,clue_state)
);
"
